<?php
/**
 * 广告模型，执行数据库中对于广告的操作
 */
class Post_model extends Model{

    function Post_model(){
        parent::Model();
    }

    /**
     * 实现书单匹配功能
     */
    function auto_match($username,$school){
        $saletable="zsaleposts_".$school;
        $wantedtable="zwantedposts_".$school;
        $status=1;   //1-有人想买我的书，有人想卖给我书
                          //2-有人想买，没人想卖
                          //3-没人想买，有人想卖
                          //4-没人想买，没人想卖
        $wanted_user=null;
        $sale_user=null;
        //对想买我书的用户按照需求数量排序
        $sale_string="SELECT * FROM $saletable WHERE user_id='{$username}' AND dealed=0";
        $sale_query = $this->db->query($sale_string);
        if( ($sale_query->row_array())==null ){
            $status=3; //没人想买我书，还未知有没人要卖给我书
        }
        else{
            $sale_result=$sale_query->result();
            $sale_string2="SELECT user_id,count(user_id) as num_wanted FROM `$wantedtable` WHERE dealed=0 AND ";
            $count=1;
            foreach($sale_result as $result){
                $book_id=$result->book_id;
                if($count==1){
                  $sale_string2.=" (book_id=$book_id ";
                  $count++;
                }
                else
                   $sale_string2.=" OR book_id=$book_id ";
            }
            $sale_string2.=") GROUP BY user_id ORDER BY count(user_id)  DESC";
            $sale_query2=$this->db->query($sale_string2);
            if($sale_query2->result()==null){
                $status=3; //没人想买我书，还未知有没人要卖给我书
            }
            else{
                $wanted_user=$sale_query2->result();  //想买我书的用户
            }
        }

        //对想卖给我书的人按需求排序
        $wanted_string="SELECT * FROM $wantedtable WHERE user_id='{$username}' AND dealed=0";
        $wanted_query = $this->db->query($wanted_string);
        if( ($wanted_query->row_array())==null ){
            if($status==3) $status=4; //没人想买我书，也没人要卖给我书
            else $status=2; //有人想买我书，但没人要卖给我书
        }
        else{
            $wanted_result=$wanted_query->result();
            $wanted_string2="SELECT user_id,count(user_id) as num_sale FROM `$saletable` WHERE dealed=0 AND";
            $count=1;
            foreach($wanted_result as $result){
                $book_id=$result->book_id;
                if($count==1){
                  $wanted_string2.=" (book_id=$book_id ";
                  $count++;
                }
                else
                   $wanted_string2.=" OR book_id=$book_id ";
            }
            $wanted_string2.=" ) GROUP BY user_id ORDER BY count(user_id)  DESC";
            $wanted_query2=$this->db->query($wanted_string2);
            if($wanted_query2->result()==null){
               if($status==3) $status=4; //没人想买我书，也没人要卖给我书
               else $status=2; //有人想买我书，但没人要卖给我书
            }
            else{
                $sale_user=$wanted_query2->result();  //想卖给我书的用户
            }
        }

        //合并两组用户，返回最后结果
        if($status==1){
            return $this->merge($wanted_user,$sale_user);
        }
        else if($status==2){
            foreach($wanted_user as $user){
                $user->num_sale=0;
            }
            return $wanted_user;
        }
        else if($status==3){
            foreach($sale_user as $user){
                $user->num_wanted=0;
            }
            return $sale_user;
        }
        else{
            return null;
        }
    }

    /**
     * 对于匹配好的用户，合并求购和出售结果后，按总和排序。返回按综合排序的用户
     */
    function merge($wanted_user,$sale_user){
        $ucount=0;
        foreach($wanted_user as $wuser){
            $wuser->num_sale=0;
            $wuser->total=$wuser->num_wanted;
            $users[$ucount++]=$wuser;  //将wanted_user对象赋值给user对象
        }
        foreach($sale_user as $suser){
            $inusers=0;  //在$users数组中没有找到相同user_id的对象
            foreach($users as $user){  //查找users中所有元素，看看是否已经包含suer对象
               if($suser->user_id==$user->user_id){
                  $user->num_sale=$suser->num_sale;
                  $user->total+=$suser->num_sale;
                  $inusers=1;
               }
            }
           if($inusers==0){    //users中没有包括suser对象，需要创建
                   $suser->num_wanted=0;
                   $suser->total=$suser->num_sale;
                   $users[$ucount++]=$suser;
           }
        }
        
        //对$users数组按照total=num_sale+numwanted 排序
        for($i=0;$i<$ucount-1;$i++){
            for($j=$i+1;$j<$ucount;$j++){
                if($users[$i]->total<$users[$j]->total){
                    $tmp=$users[$i];
                    $users[$i]=$users[$j];
                    $users[$j]=$tmp;
                }
            }
        }
        return array_slice($users, 0, 20);   //只返回前20个结果，否则返回所有用户，数据量很大。
    }

    /**
     * 查找匹配好的用户，匹配书籍的具体情况
     */
    function automatch_detail($username,$matched_user,$school){
        $saletable="zsaleposts_".$school;
        $wantedtable="zwantedposts_".$school;
        $dataarray=null;
        
        //看看$matched_user想卖给我什么书
        $wanted_string="SELECT * FROM $wantedtable WHERE user_id='{$username}' AND dealed=0";
        $wanted_query = $this->db->query($wanted_string);
        if( ($wanted_query->row_array())==null ){
            $dataarray['saleposts']=null;
        }
        else{
            $wanted_result=$wanted_query->result();
            $wanted_string2="SELECT * FROM `$saletable` WHERE user_id='$matched_user' AND dealed=0 AND ( ";
            $count=1;
            foreach($wanted_result as $result){
                $book_id=$result->book_id;
                if($count==1){
                  $wanted_string2.=" book_id=$book_id ";
                  $count++;
                }
                else
                   $wanted_string2.=" OR book_id=$book_id ";
            }
            $wanted_string2.=")";
            $wanted_query2=$this->db->query($wanted_string2);
            $dataarray['saleposts']= $wanted_query2->result();
        }
        
        //看看matched_user想买我什么书
        $sale_string="SELECT * FROM $saletable WHERE user_id='{$username}' AND dealed=0";
        $sale_query = $this->db->query($sale_string);
        if( ($sale_query->row_array())==null ){
            $dataarray['wantedposts']=null;
        }
        else{
            $sale_result=$sale_query->result();
            $sale_string2="SELECT * FROM `$wantedtable` WHERE user_id='$matched_user' AND dealed=0 AND ( ";
            $count=1;
            foreach($sale_result as $result){
                $book_id=$result->book_id;
                if($count==1){
                  $sale_string2.=" book_id=$book_id ";
                  $count++;
                }
                else
                   $sale_string2.=" OR book_id=$book_id ";
            }
            $sale_string2.=")";
            $sale_query2=$this->db->query($sale_string2);
            $dataarray['wantedposts']= $sale_query2->result();
        }
        return $dataarray;
    }

    /**
     *获取自己的出售广告
     */
    function get_mysalepost($total,$username,$school,$dealed,$offset,$perpage){
        $adtable="zsaleposts_".$school;
        if($total==1)
           $post_query_string="SELECT count(*) as number FROM book b
                                      JOIN $adtable p ON ( p.book_id = b.id ) 
                                      WHERE p.user_id='$username' AND dealed=$dealed ";
        else
           $post_query_string="SELECT * FROM book b
                                      JOIN $adtable p ON ( p.book_id = b.id ) 
                                      WHERE p.user_id='$username' AND dealed=$dealed 
                                      ORDER BY postdate DESC LIMIT $offset,$perpage ";

        $post_query = $this->db->query($post_query_string);
        if( ($post_query->row_array())==null ){
            return null;
        }
        else{
            $post_result=$post_query->result();
            return $post_result;
        }       
    }

    /**
     *置顶自己的出售广告
     */
    function up_mysalepost($username,$bookid,$upall,$school){
        $adtable="zsaleposts_".$school;
        if($upall==0){
           $post_query_string="UPDATE $adtable SET postdate=CURRENT_TIMESTAMP WHERE
                                          user_id='$username' AND book_id=$bookid";
           $post_query = $this->db->query($post_query_string);
           return 1;
        }
        else if($upall==1){ //置顶全部
           $sql="SELECT money from user WHERE id='$username'";
           $query=$this->db->query($sql);
           $result=$query->result();
           if($result[0]->money<3)
             return 0;
           else{
             $post_query_string="UPDATE $adtable SET postdate=CURRENT_TIMESTAMP WHERE
                                          user_id='$username' ";
             $post_query = $this->db->query($post_query_string);
             $sql2="UPDATE user SET money=money-3 WHERE id='$username'";
             $query2=$this->db->query($sql2);
             return 1;
           }
        }
        
    }

    /**
     *获取自己的求购广告
     */
    function get_mywantedpost($total,$username,$school,$dealed,$offset,$perpage){
        $adtable="zwantedposts_".$school;
        if($total==1)
           $post_query_string="SELECT count(*) as number FROM book b
                                      JOIN $adtable p ON ( p.book_id = b.id )
                                      WHERE p.user_id='$username' AND dealed=$dealed ";
        else
           $post_query_string="SELECT * FROM book b
                                      JOIN $adtable p ON ( p.book_id = b.id )
                                      WHERE p.user_id='$username' AND dealed=$dealed
                                      ORDER BY postdate DESC LIMIT $offset,$perpage ";
        $post_query = $this->db->query($post_query_string);
        if( ($post_query->row_array())==null ){
            return null;
        }
        else{
            $post_result=$post_query->result();
            return $post_result;
        }
    }

    /**
     *置顶自己求购书籍广告
     */
    function up_mywantedpost($username,$bookid,$upall,$school){
        $adtable="zwantedposts_".$school;
        if($upall==0){
           $post_query_string="UPDATE $adtable SET postdate=CURRENT_TIMESTAMP WHERE
                                          user_id='$username' AND book_id=$bookid";
           $post_query = $this->db->query($post_query_string);
           return 1;
        }
        else if($upall==1){
           $sql="SELECT money from user WHERE id='$username'";
           $query=$this->db->query($sql);
           $result=$query->result();
           if($result[0]->money<3)
             return 0;
           else{
             $post_query_string="UPDATE $adtable SET postdate=CURRENT_TIMESTAMP WHERE
                                          user_id='$username' ";
             $post_query = $this->db->query($post_query_string);
             $sql2="UPDATE user SET money=money-3 WHERE id='$username'";
             $query2=$this->db->query($sql2);
             return 1;
           }
        }
    }


    /**
     * 向数据库插入出售广告条目
     */
    function insert_salepost($post_data,$school){
        $posttable="zsaleposts_".$school;
        $data = array(
            'user_id'=>$post_data['user_id'],
            'book_id' => $post_data['book_id'] ,
            'book_title' => $post_data['book_title'] ,
            'price' => $post_data['price'] ,
            'quality' => $post_data['quality'] ,
            'other'=>$post_data['other'],
            'phone'=>$post_data['phone'],
            'email'=>$post_data['email'],
            'qq'=>$post_data['qq'],
        );
        $sql="SELECT * FROM $posttable WHERE user_id='{$data['user_id']}' AND book_id='{$data['book_id']}'";
        $query=$this->db->query($sql);
        if($this->db->affected_rows()==1){  //数据库中这个用户已经发布过相同的这本书
            $result=$query->result();
            $num=$result[0]->num+1;
            $price=$data['price'];
            $quality=$data['quality'];
            $other=$data['other'];
            $phone=$data['phone'];
            $email=$data['email'];
            $qq=$data['qq'];
            $sql2="UPDATE $posttable SET num=$num , price=$price , quality=$quality, other='$other',phone='$phone' , email='$email' , qq='$qq'
                      WHERE user_id='{$data['user_id']}' AND book_id='{$data['book_id']}' ; ";
            $query=$this->db->query($sql2);
            $sql3=" UPDATE user SET numsales=numsales+1 WHERE id='{$data['user_id']}'; ";
            $query=$this->db->query($sql3);
            return 1;
        }
       else{
           $this->db->insert($posttable, $data);    //插入数据
           if(($this->db->affected_rows())!=1){
             return 0;      //如果插入操作影响的行数不为1，则表示插入失败，返回0
           }
           else{
            $sql4="UPDATE user SET numsales=numsales+1 WHERE id='{$data['user_id']}';";
             $query4=$this->db->query($sql4);
            $sql5="UPDATE book SET num_sales=num_sales+1 WHERE id='{$data['book_id']}';";
            $query5=$this->db->query($sql5);
            return 1;    //如果插入成功，返回1
           }
        }
    }

    /**
     * 向数据库插入求购广告条目
     */
    function insert_wantedpost($post_data,$school){
        $posttable="zwantedposts_".$school;
        $data = array(
            'user_id'=>$post_data['user_id'],
            'book_id' => $post_data['book_id'] ,
            'book_title' => $post_data['book_title'] ,
            'start_price' => $post_data['start_price'] ,
            'end_price' => $post_data['end_price'] ,
            'other'=>$post_data['other'],
            'phone'=>$post_data['phone'],
            'email'=>$post_data['email'],
            'qq'=>$post_data['qq'],
        );
        $sql="SELECT * FROM $posttable WHERE user_id='{$data['user_id']}' AND book_id='{$data['book_id']}'";
        $query=$this->db->query($sql);
        if($this->db->affected_rows()==1){  //数据库中这个用户已经发布过相同的这本书
            $result=$query->result();
            $num=$result[0]->num+1;
            $start_price=$data['start_price'];
            $end_price=$data['end_price'];
            $other=$data['other'];
            $phone=$data['phone'];
            $email=$data['email'];
            $qq=$data['qq'];
            $sql2="UPDATE $posttable SET num=$num , start_price=$start_price ,end_price=$end_price, other='$other',phone='$phone' , email='$email' , qq='$qq'
                       WHERE user_id='{$data['user_id']}' AND book_id='{$data['book_id']}' ";
            $query=$this->db->query($sql2);
            $sql3="UPDATE user SET numwanted=numwanted+1 WHERE id='{$data['user_id']}' ; ";
            $query=$this->db->query($sql3);
            return 1;
        }
       else{
           $this->db->insert($posttable, $data);    //插入数据
           if(($this->db->affected_rows())!=1){
             return 0;      //如果插入操作影响的行数不为1，则表示插入失败，返回0
           }
           else{
             $sql4="UPDATE user SET numwanted=numwanted+1 WHERE id='{$data['user_id']}';";
             $query4=$this->db->query($sql4);
             $sql5="UPDATE book SET num_wanted=num_wanted+1 WHERE id='{$data['book_id']}';";
             $query5=$this->db->query($sql5);
            return 1;    //如果插入成功，返回1
           }
        }
    }

    /**
     * 获取某本书所有出售广告
     */
    function getall_salepost($bookid,$school){
        $posttable="zsaleposts_".$school;
        $post_query_string="SELECT * FROM $posttable WHERE boo_id=$bookid AND dealed=0 ORDER By postdate DESC price ASC";
        $query = $this->db->query($post_query_string);
        if( ($query->row_array())==null ){
            return null;
        }
        else{
            $result=$query->result();
            return $result;
        }
    }

    /**
     * 获取某本书所有待售广告
     */
    function getall_wantedpost($bookid,$school){
        $posttable="zwantedposts_".$school;
        $post_query_string="SELECT * FROM $posttable WHERE boo_id=$bookid AND dealed=0 ORDER By postpostate DESC price ASC";
        $query = $this->db->query($post_query_string);
        if( ($query->row_array())==null ){
            return null;
        }
        else{
            $result=$query->result();
            return $result;
        }
    }

     /**
      * 获取最新出售广告
      */
     function getlatest_salepost($total,$school,$offset,$perpage){
        $posttable="zsaleposts_".$school;
         if($total==1)
           $post_query_string="SELECT count(*) as number FROM $posttable WHERE dealed=0 ";
        else
           $post_query_string="SELECT * FROM $posttable WHERE dealed=0 ORDER BY postdate DESC  LIMIT $offset,$perpage";
        $query = $this->db->query($post_query_string);
        if( ($query->row_array())==null ){
            return null;
        }
        else{
            $result=$query->result();
            return $result;
        }
    }

    /**
     * 获取最新求购广告
     */
    function getlatest_wantedpost($total,$school,$offset,$perpage){
        $posttable="zwantedposts_".$school;
        if($total==1)
           $post_query_string="SELECT count(*) as number FROM $posttable WHERE dealed=0";
        else
           $post_query_string="SELECT * FROM $posttable WHERE dealed=0 ORDER By postdate DESC  LIMIT $offset,$perpage";
        $query = $this->db->query($post_query_string);
        if( ($query->row_array())==null ){
            return null;
        }
        else{
            $result=$query->result();
            return $result;
        }
    }

    /**
     * 获取出售广告的细节
     */
    function get_salepost_detail($school,$userid,$bookid){
        $posttable="zsaleposts_".$school;
        $post_query_string="SELECT * FROM $posttable WHERE user_id='$userid' AND book_id='$bookid' ";
        $query = $this->db->query($post_query_string);
        if( ($query->row_array())==null ){
            return null;
        }
        else{
            $result=$query->result();
            return $result[0];
        }
    }

    /**
     * 获取求购广告的细节
     */
    function get_wantedpost_detail($school,$userid,$bookid){
        $posttable="zwantedposts_".$school;
        $post_query_string="SELECT * FROM $posttable WHERE user_id='$userid' AND book_id='$bookid' ";
        $query = $this->db->query($post_query_string);
        if( ($query->row_array())==null ){
            return null;
        }
        else{
            $result=$query->result();
            return $result[0];
        }
    }

    /**
     *获取一本书的所有出售广告
     */
    function get_salepost_ofbook($school,$bookid,$total,$offset,$perpage){
        $posttable="zsaleposts_".$school;
        if($total==1)
           $post_query_string="SELECT count(*) as number FROM $posttable where book_id=$bookid ";
        else
           $post_query_string="SELECT * FROM $posttable where book_id=$bookid AND dealed=0 ORDER By postdate DESC  LIMIT $offset,$perpage";
        $query = $this->db->query($post_query_string);
        if( ($query->row_array())==null ){
            return null;
        }
        else{
            $result=$query->result();
            return $result;
        }
    }

    /**
     *获取一本书所有的求购广告
     */
    function get_wantedpost_ofbook($school,$bookid,$total,$offset,$perpage){
        $posttable="zwantedposts_".$school;
        if($total==1)
           $post_query_string="SELECT count(*) as number FROM $posttable where book_id=$bookid ";
        else
           $post_query_string="SELECT * FROM $posttable where book_id=$bookid AND dealed=0 ORDER By postdate DESC  LIMIT $offset,$perpage";
        $query = $this->db->query($post_query_string);
        if( ($query->row_array())==null ){
            return null;
        }
        else{
            $result=$query->result();
            return $result;
        }
    }
   
   /**
    * 修改广告状态为已经交易
    */
   function bookdealed($type,$userid,$bookid,$school){
        if($type=="sale")
           $adtable="zsaleposts_".$school;
        else if($type=="wanted")
           $adtable="zwantedposts_".$school;
        $post_query_string="UPDATE $adtable SET dealed=1
                                      WHERE user_id='$userid' AND book_id='$bookid' ";
        $post_query = $this->db->query($post_query_string);

        if($type=="sale")
           $sql="UPDATE user SET numsales=numsales-1, numsaledeals=numsaledeals+1 WHERE id='$userid' ";
        else if($type=="wanted")
           $sql="UPDATE user SET numwanted=numwanted-1, numwanteddeals=numwanteddeals+1 WHERE id='$userid' ";
        $query = $this->db->query($sql);

        if($type=="sale")
          $sql2="UPDATE book SET num_sales=num_sales-1, numdeals=numdeals+1 where id='$bookid'";
        if($type=="wanted")
          $sql2="UPDATE book SET num_wanted=num_wanted-1, numdeals=numdeals+1 where id='$bookid'";
        $query2=$this->db->query($sql2);
        return 1;
   }

}

?>
